<?php
/**
 * TomatoCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@tomatocms.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2009-2010 TIG Corporation (http://www.tig.vn)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @version 	$Id: Category.php 5062 2010-08-28 18:52:54Z huuphuoc $
 * @since		2.0.5
 */

class Category_Models_Dao_Pdo_Mysql_Category extends Tomato_Model_Dao
	implements Category_Models_Interface_Category
{
	public function convert($entity) 
	{
		return new Category_Models_Category($entity); 
	}
	
	public function getById($id) 
	{
		$select = $this->_conn
						->select()
						->from(array('c' => $this->_prefix . 'category'))
						->where('c.category_id = ?', $id)
						->limit(1);
		$row = $select->query()->fetch();
		return (null == $row) ? null : new Category_Models_Category($row);
	}
	
	public function getSubCategories($categoryId) 
	{
		$select = $this->_conn
						->select()
						->from(array('c' => $this->_prefix . 'category'))
						/**
						 * @since 2.0.8
						 */
						->where('c.language = ?', $this->_lang)
						->where('c.parent_id = ?', $categoryId);
		$rs = $select->query()->fetchAll();
		return new Tomato_Model_RecordSet($rs, $this);
	}
	
	public function add($category) 
	{
		$parentId = $category->parent_id;
		if ($parentId) {
			$sql = 'SELECT right_id FROM ' . $this->_prefix . 'category WHERE category_id = ' . $this->_conn->quote($parentId);
		} else {
			$sql = 'SELECT MAX(right_id) AS right_id FROM ' . $this->_prefix . 'category';
		}
		$right   = $this->_conn->query($sql)->fetch();
		$rightId = ($parentId) ? $right->right_id : $right->right_id + 1;
		if ($rightId != null) {
			$sql = 'UPDATE ' . $this->_prefix . 'category 
					SET left_id = IF(left_id > ?, left_id + 2, left_id), 
						right_id = IF(right_id >= ?, right_id + 2, right_id)';
			$this->_conn->query($sql, array($rightId, $rightId));
			
			$data = array(
				'name'		   => $category->name,
				'slug'		   => $category->slug,
				'meta'		   => $category->meta,
				'created_date' => $category->created_date,
				'user_id'	   => $category->user_id,
				'left_id'	   => $rightId,
				'right_id'	   => $rightId + 1,
				'parent_id'	   => $parentId,
			
				/**
				 * @since 2.0.8
				 */
				'language'     => $category->language,
			);
			
			/**
			 * Keep the category Id in case user update its parent
			 * See Category_CategoryController::editAction()
			 */
			if (isset($category->category_id) && $category->category_id != null) {
				$data['category_id'] = $category->category_id;
				$this->_conn->insert($this->_prefix . 'category', $data);
				return $category->category_id;				
			} else {
				$this->_conn->insert($this->_prefix . 'category', $data);
				return $this->_conn->lastInsertId($this->_prefix . 'category');
			}
		}
	}
	
	public function update($category) 
	{
		$where[] = 'category_id = ' . $this->_conn->quote($category->category_id);
		$this->_conn->update($this->_prefix . 'category', array(
								'name' 			=> $category->name,
								'slug' 			=> $category->slug,
								'meta' 			=> $category->meta,
								'modified_date' => $category->modified_date,
		
								/**
								 * @since 2.0.8
								 */
								'language'      => $category->language,
							), $where);
	}
	
	public function delete($category) 
	{
		$where[] = 'category_id = ' . $this->_conn->quote($category->category_id);
		$this->_conn->delete($this->_prefix . 'category', $where);
		
		$sql = 'UPDATE ' . $this->_prefix . 'category 
				SET left_id = left_id - 1, right_id = right_id - 1
				WHERE left_id BETWEEN ? AND ?';
		$this->_conn->query($sql, array($category->left_id, $category->right_id));

		$sql = 'UPDATE ' . $this->_prefix . 'category 
				SET right_id = right_id - 2
				WHERE right_id > ?';
		$this->_conn->query($sql, array($category->right_id));
		
		$sql = 'UPDATE ' . $this->_prefix . 'category 
				SET left_id = left_id - 2
				WHERE left_id > ?';
		$this->_conn->query($sql, array($category->right_id));
	}
	
	public function getTree()
	{
		$select = $this->_conn
						->select()
						->from(array('node' => $this->_prefix . 'category'))
						->from(array('parent' => $this->_prefix . 'category'), array('depth' => '(COUNT(parent.name) - 1)'))
						->where('node.left_id BETWEEN parent.left_id AND parent.right_id')
						/**
						 * @since 2.0.8
						 */
						->where('node.language = ?', $this->_lang)
						->where('parent.language = ?', $this->_lang)
						->group('node.category_id')
						->order('node.left_id');
						
		$rs = $select->query()->fetchAll();
		return new Tomato_Model_RecordSet($rs, $this);
	}
	
	public function getParents($categoryId)
	{
		$select = $this->_conn
						->select()
						->from(array('node' => $this->_prefix . 'category'), array())
						->from(array('parent' => $this->_prefix . 'category'))
						->where('node.left_id BETWEEN parent.left_id AND parent.right_id')
						->where('node.category_id = ?', $categoryId)
						->order('parent.left_id');
						
		$rs = $select->query()->fetchAll();
		return new Tomato_Model_RecordSet($rs, $this);
	}
	
	public function getParentId($categoryId)
	{
		$select = $this->_conn
						->select()
						->from(array('c1' => $this->_prefix . 'category'), array())
						->joinInner(array('c2' => $this->_prefix . 'category'), 'c1.left_id BETWEEN c2.left_id AND c2.right_id', array('category_id'))
						->where('c1.category_id = ?', $categoryId)
						->where('c2.category_id <> ?', $categoryId);
		$rs = $select->query()->fetchAll();
		return (count($rs) == 0) ? $categoryId : $rs[0]->category_id;		
	}
	
	public function updateOrder($category)
	{
		$where[] = 'category_id = ' . $this->_conn->quote($category->category_id);
		$this->_conn->update($this->_prefix . 'category', array(
							'parent_id' => $category->parent_id,
							'left_id'   => $category->left_id,
							'right_id'  => $category->right_id,
						), $where);
	}
	
	/* ========== For translation =========================================== */
	
	public function getTranslations($item)
	{
		$sql = 'SELECT c.* FROM ' . $this->_prefix . 'category AS c
				INNER JOIN 
				(
					SELECT tr1.* FROM ' . $this->_prefix . 'core_translation AS tr1
					INNER JOIN ' . $this->_prefix . 'core_translation AS tr2 
						ON (tr1.item_id = ? AND tr1.source_item_id = tr2.item_id) 
						OR (tr2.item_id = ? AND tr1.item_id = tr2.source_item_id)
						OR (tr1.source_item_id = ? AND tr1.source_item_id = tr2.source_item_id)
					WHERE tr1.item_class = ? AND tr2.item_class = ?
					GROUP by tr1.translation_id
				) AS tr
					ON tr.item_id = c.category_id';
		$rs = $this->_conn->query($sql, array(
										$item->category_id, $item->category_id, $item->category_id, 
										'Category_Models_Category', 'Category_Models_Category'
								))->fetchAll();
		return (null == $rs) ? null : new Tomato_Model_RecordSet($rs, $this);
	}
	
	public function getTranslatable($lang)
	{
		$sql = 'SELECT c.*, (tr.item_id IS NULL) AS translatable
				FROM
				(
					SELECT node.category_id, node.name, node.slug, (COUNT(parent.name) - 1) AS depth, 
						node.left_id, node.right_id, node.parent_id
					FROM ' . $this->_prefix . 'category AS node, 
						' . $this->_prefix . 'category AS parent
					WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
						AND node.language = ?
						AND parent.language = ?
					GROUP BY node.category_id
					ORDER BY node.left_id
				) AS c
				LEFT JOIN ' . $this->_prefix . 'core_translation AS tr
					ON tr.source_item_id = c.category_id
					AND tr.item_class = ?
					AND tr.source_language = ?
					AND tr.language = ?';
				
		$rs  = $this->_conn->query($sql, array(
									$this->_lang, $this->_lang,
									'Category_Models_Category', 
									$this->_lang, $lang
								))->fetchAll();
		return new Tomato_Model_RecordSet($rs, $this);
	}
	
	public function getSource($category)
	{
		$select = $this->_conn
						->select()
						->from(array('c' => $this->_prefix . 'category'))
						->joinInner(array('tr' => $this->_prefix . 'core_translation'), 'c.category_id = tr.source_item_id', array())
						->where('tr.item_id = ?', $category->category_id)
						->where('tr.item_class = ?', 'Category_Models_Category')
						->limit(1);
		$row = $select->query()->fetch();
		return (null == $row) ? null : new Category_Models_Category($row);
	}
}
